import 'dart:async';
import 'dart:io';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

class DatabaseHelper {
  static final DatabaseHelper _instance = DatabaseHelper._internal();
  static DatabaseHelper get instance => _instance;

  static Database? _database;

  DatabaseHelper._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    try {
      _database = await _initDatabase();
      return _database!;
    } catch (e) {
      print('Error initializing database: $e');
      // 如果数据库初始化失败，尝试删除旧数据库并重新创建
      await deleteDatabase();
      _database = await _initDatabase();
      return _database!;
    }
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'rural_revitalization.db');
    return await openDatabase(
      path,
      version: 4, // Increase version number for user table updates
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  Future<void> _onCreate(Database db, int version) async {
    // Create users table
    await db.execute('''
      CREATE TABLE users(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        username TEXT NOT NULL,
        password TEXT NOT NULL,
        isAdmin INTEGER NOT NULL,
        age INTEGER,
        gender TEXT
      )
    ''');

    // Create information table
    await db.execute('''
      CREATE TABLE information(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        title TEXT NOT NULL,
        content TEXT NOT NULL,
        imageUrl TEXT,
        publishDate TEXT NOT NULL,
        publisherId INTEGER NOT NULL,
        FOREIGN KEY (publisherId) REFERENCES users (id)
      )
    ''');

    // Create products table
    await db.execute('''
      CREATE TABLE products(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT NOT NULL,
        price REAL NOT NULL,
        imageUrl TEXT,
        stock INTEGER NOT NULL,
        publisherId INTEGER NOT NULL,
        FOREIGN KEY (publisherId) REFERENCES users (id)
      )
    ''');

    // Create orders table
    await db.execute('''
      CREATE TABLE orders(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        userId INTEGER NOT NULL,
        totalAmount REAL NOT NULL,
        status TEXT NOT NULL,
        orderDate TEXT NOT NULL,
        FOREIGN KEY (userId) REFERENCES users (id)
      )
    ''');

    // Create order items table
    await db.execute('''
      CREATE TABLE order_items(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        orderId INTEGER NOT NULL,
        productId INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        price REAL NOT NULL,
        FOREIGN KEY (orderId) REFERENCES orders (id),
        FOREIGN KEY (productId) REFERENCES products (id)
      )
    ''');

    // Create forum messages table
    await db.execute('''
      CREATE TABLE forum_messages(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        content TEXT NOT NULL,
        publishDate TEXT NOT NULL,
        userId INTEGER NOT NULL,
        username TEXT NOT NULL,
        FOREIGN KEY (userId) REFERENCES users (id)
      )
    ''');

    // Create banners table
    await db.execute('''
      CREATE TABLE banners(
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        imageUrl TEXT NOT NULL,
        description TEXT NOT NULL,
        websiteUrl TEXT NOT NULL,
        publisherId INTEGER NOT NULL,
        FOREIGN KEY (publisherId) REFERENCES users (id)
      )
    ''');
  }

  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // Add forum_messages table if upgrading from version 1 to 2
      await db.execute('''
        CREATE TABLE forum_messages(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          content TEXT NOT NULL,
          publishDate TEXT NOT NULL,
          userId INTEGER NOT NULL,
          username TEXT NOT NULL,
          FOREIGN KEY (userId) REFERENCES users (id)
        )
      ''');
    }

    if (oldVersion < 3) {
      // Add banners table if upgrading from version 2 to 3
      await db.execute('''
        CREATE TABLE banners(
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          imageUrl TEXT NOT NULL,
          description TEXT NOT NULL,
          websiteUrl TEXT NOT NULL,
          publisherId INTEGER NOT NULL,
          FOREIGN KEY (publisherId) REFERENCES users (id)
        )
      ''');
    }

    if (oldVersion < 4) {
      // Add age and gender columns to users table if upgrading from version 3 to 4
      await db.execute('ALTER TABLE users ADD COLUMN age INTEGER');
      await db.execute('ALTER TABLE users ADD COLUMN gender TEXT');
    }
  }

  // For development purposes only - delete the database to start fresh
  Future<void> deleteDatabase() async {
    String path = join(await getDatabasesPath(), 'rural_revitalization.db');
    File file = File(path);
    if (await file.exists()) {
      await file.delete();
      _database = null;
    }
  }
}
